package cn.melon.mod.sc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.melon.Helper.DBHelper;
import cn.melon.base.IBaseExtractor;
import net.ucanaccess.jdbc.UcanaccessSQLException;

public class IScDaolmpl implements IScDao {

    public String sqlCreate = "CREATE TABLE course" 
                    + "(class_term TEXT(20)," 
                    + "class_number TEXT(20),"
                    + "class_name TEXT(10)," 
                    + "teacher_name TEXT(10)," 
                    + "class_time TEXT(10)," 
                    + "classroom TEXT(10)"
                    + ")";

    private DBHelper<ScInfo> dbHelper;
    private IBaseExtractor<ScInfo> extractor = new IBaseExtractor<ScInfo>() {

        @Override
        public List<ScInfo> convertToList(ResultSet rs) {

            if (rs == null) {
                return null;
            }

            List<ScInfo> infoList = new ArrayList<ScInfo>();
            try {
                while (rs.next()) {
                    ScInfo info = new ScInfo();

                    info.setClassNumber(rs.getString("class_number"));
                    info.setClassName(rs.getString("class_name"));
                    info.setClassTeacher(rs.getString("teacher_name"));
                    info.setClassroom(rs.getString("classroom"));
                    info.setClassTime(rs.getString("class_time"));
                    info.setClassTerm(rs.getString("class_term"));
                    infoList.add(info);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return infoList;
        }
    };

    public IScDaolmpl() {
        dbHelper = new DBHelper<ScInfo>();
    }
    
    public void onCreate() {
        try {
            dbHelper.sqlExecute(sqlCreate, new String[]{});
        } catch (UcanaccessSQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public boolean addInfo(ScInfo info) {
        String insertSql = "INSERT INTO course " 
                        + "(class_term, class_number, class_name,"
                        + "teacher_name, classroom, class_time)"
                        + "VALUES" 
                        + "(?, ?, ?, ?, ?, ?)";

        String[] paras = new String[6];
        paras[0] = info.getClassTerm();
        paras[1] = info.getClassNumber();
        paras[2] = info.getClassName();
        paras[3] = info.getClassTeacher();
        paras[4] = info.getClassroom();
        paras[5] = info.getClassTime();

        return dbHelper.sqlUpdate(insertSql, paras);
    }

    @Override
    public void deleteInfo(String classNumber) {
        String sqlDelete = "DELETE FROM course WHERE class_number = ?";
        dbHelper.sqlUpdate(sqlDelete, new String[] { classNumber });
    }

    @Override
    public ScInfo getInfo(String classNumber) {
        String sqlQuery = "SELECT * FROM course where class_number = ?";
        String[] paras = new String[] { classNumber };

        List<ScInfo> infoList = null;
        try {
            infoList = dbHelper.sqlQuery(sqlQuery, paras, extractor);
        } catch (UcanaccessSQLException e) {
            e.printStackTrace();
        }

        if (infoList != null && infoList.size() > 0) {
            return infoList.get(0);
        } else {
            return null;
        }
    }

    @Override
    public List<ScInfo> getAllScInfo() {
        String sqlQuery = "SELECT * FROM course";

        try {
            return dbHelper.sqlQuery(sqlQuery, new String[] {}, extractor);
        } catch (UcanaccessSQLException e) {
            e.printStackTrace();
        }
        return null;

    }

    @Override
    public boolean changeInfo(String classNumber, ScInfo info) {
        String sqlDelete = "update course set class_term= ? ,class_name= ?, teacher_name = ? ,classroom= ? ,class_time= ?  where class_number= ? ;";
        return dbHelper.sqlUpdate(sqlDelete, new String[] { info.getClassTerm(), info.getClassName(), info.getClassTeacher(),
                info.getClassroom(), info.getClassTime(), info.getClassNumber() });
    }

    public static void main(String[] args) {
        IScDaolmpl scDaolmpl = new IScDaolmpl();
        ScInfo info = new ScInfo();
        info.setClassName("微型机系统与接口技术");
        info.setClassNumber("2");
        info.setClassroom("九龙湖教四-104");
        info.setClassTeacher("杨全胜");
        info.setClassTerm("16-17-2");
        info.setClassTime("星期四-1-2");

        //scDaolmpl.addInfo(info);
        
        //scDaolmpl.deleteInfo("2");
        
        List<ScInfo> infoList = scDaolmpl.getAllScInfo();
        for(ScInfo tmpInfo : infoList) {
            System.out.println(tmpInfo.getClassName());
        }
//        scDaolmpl.onCreate();
        
        
        
        
    }
}
